﻿using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Com.JunCaiSoft.Common.AssistClass
{
    public static class SqlQueryAssist
    {
        public static IList<T> SqlQuery<T>(DbContext db, string sql, params object[] parameters)
            where T : class, new()
        {
            //注意：不要对GetDbConnection获取到的conn进行using或者调用Dispose，否则DbContext后续不能再进行使用了，会抛异常
            var conn = db.Database.GetDbConnection();
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters);
                    var propts = typeof(T).GetProperties();
                    var rtnList = new List<T>();
                    T model;
                    object val;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            model = new T();
                            foreach (var l in propts)
                            {
                                val = reader[l.Name];
                                if (val == DBNull.Value)
                                {
                                    l.SetValue(model, null);
                                }
                                else
                                {
                                    l.SetValue(model, val);
                                }
                            }
                            rtnList.Add(model);
                        }
                    }
                    return rtnList;
                }
            }
            catch(Exception e)
            {
                throw e;
            }
            //finally
            //{
            //    conn.Close();
            //}
        }

        public static async Task<IList<T>> SqlQueryAsync<T>(DbContext db, string sql, params object[] parameters)
            where T : class, new()
        {
            //注意：不要对GetDbConnection获取到的conn进行using或者调用Dispose，否则DbContext后续不能再进行使用了，会抛异常
            var conn = db.Database.GetDbConnection();
            try
            {
                if(conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters);
                    var propts = typeof(T).GetProperties();
                    var rtnList = new List<T>();
                    T model;
                    object val;
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            model = new T();
                            foreach (var item in propts)
                            {
                                val = reader[item.Name];
                                if (item.CanWrite)
                                {
                                    if (val == DBNull.Value)
                                    {
                                        item.SetValue(model, null);
                                    }
                                    else
                                    {
                                        item.SetValue(model, val);
                                    }
                                }
                            }
                            rtnList.Add(model);
                        }
                    }
                    return rtnList;
                }
            }
            catch(Exception e)
            {
                throw e;
            }
            //finally
            //{
            //    conn.Close();
            //}
        }
    }
}
